package hu.veilingsite.server;

import hu.veilingsite.shared.Auction;
import hu.veilingsite.shared.Bid;
import hu.veilingsite.shared.Category;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

public class VeilingSiteDAOOracle implements VeilingsiteDAO {
	private static final String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ondora01.hu.nl) (PORT=8521))(CONNECT_DATA=(SERVICE_NAME=cursus01.hu.nl)))";
	private static final String username = "THO5_2011_3A_TEAM4";
	private static final String password = "THO5_2011_3A_TEAM4";
	
	private Connection connection;
	//User
	private PreparedStatement insertUser;
	private PreparedStatement saveUser;
	private PreparedStatement getUser;
	private PreparedStatement getUsers;
	private PreparedStatement getBidsFromUser;
	private PreparedStatement getAuctionsFromUser;
	private PreparedStatement getCreditsFromUser;
	private PreparedStatement getNameOfUser;
	//Auction
	private PreparedStatement insertAuction;
	private PreparedStatement saveAuction;
	private PreparedStatement getAuction;
	private PreparedStatement getAuctions;
	private PreparedStatement getAuctionsadmin;
	private PreparedStatement getBidsFromAuction;
	private PreparedStatement deleteAuction;
	private PreparedStatement deleteAllBidsFromAuction;
	//Bid
	private PreparedStatement insertBid;
	private PreparedStatement saveBid;
	private PreparedStatement deleteBid;
	private PreparedStatement getBid;
	private PreparedStatement getUserFromBid;
	private PreparedStatement getHighestBid;
	//Category
	private PreparedStatement insertCategory;
	private PreparedStatement saveCategory;
	private PreparedStatement deleteCategory;
	private PreparedStatement getCategory;
	private PreparedStatement getCategorys;
	//coupons
	private PreparedStatement getAantalCredits;
	private PreparedStatement deleteCoupon;
	//rest
	private PreparedStatement getYearsStart;
	private PreparedStatement getYearsEnd;
	private PreparedStatement getDays;
	
	public void connect(){
		try {
			String driverName = "oracle.jdbc.driver.OracleDriver";
		    Class.forName(driverName);
		    connection = DriverManager.getConnection(url,username,password);
		} catch (ClassNotFoundException cnfe) {
			System.out.println("Driver missing");
			//cnfe.printStackTrace();
			// Could not find the database driver
		} catch (SQLException se) {
			System.out.println("DB se");
			se.printStackTrace();
			// Could not connect to the database
		}
	}
	
	public VeilingSiteDAOOracle(){
		
	}
	
	public boolean closeConnection() {
		try {
			connection.close();
			return true;
		} catch (SQLException e) {
			System.out.println("closeConnection");
			e.printStackTrace();
			return false;
		}
	}
	
	@Override
	public Auction getAuction(int auctionId) {
		connect();
		Auction auction = null;
		try {
			getAuction = connection.prepareStatement("SELECT * FROM auction WHERE id=?");
			getAuction.setInt(1,auctionId);
			ResultSet rs = getAuction.executeQuery();
			rs.next();
			auction = new Auction(auctionId,rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getDate(7),rs.getInt(8));
		} catch (SQLException e) {
			System.out.println("getAuction");
			e.printStackTrace();
		}
		closeConnection();
		return auction;
	}

	@Override
	public ArrayList<Auction> getAuctionsFromUser(int userId) {
		connect();
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			getAuctionsFromUser = connection.prepareStatement("SELECT id,authorId,categoryId,title,description,startprice,endDate,visible FROM Auction,Users WHERE authorId=userid AND userid=?");
			getAuctionsFromUser.setInt(1,userId);
			ResultSet rs = getAuctionsFromUser.executeQuery();
			while (rs.next()) {
				auctions.add(new Auction(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getDate(7),rs.getInt(8)));
			}
		} catch (SQLException e) {
			System.out.println("getAuctionsFromUser");
			e.printStackTrace();
		}
		closeConnection();
		return auctions;
	}

	@Override
	public ArrayList<Auction> getLatestAuctions() {
		connect();
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			getAuctionsFromUser = connection.prepareStatement("SELECT id,authorId,categoryId,title,description,startprice,endDate,visible FROM Auction WHERE enddate > sysdate ORDER BY enddate");
			ResultSet rs = getAuctionsFromUser.executeQuery();
			int count = 0;
			while (rs.next() && count < 3) {
				auctions.add(new Auction(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getDate(7),rs.getInt(8)));
				count++;
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println("getAuctionsFromUser");
			e.printStackTrace();
		}
		closeConnection();
		return auctions;
	}

	@Override
	public Bid getBid(int bidId) {
		connect();
		Bid bid = null;
		try {
			getBid = connection.prepareStatement("SELECT * FROM Bid WHERE id=?");
			getBid.setInt(1,bidId);
			ResultSet rs = getBid.executeQuery();
			rs.next();
			bid = new Bid(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getDate(5));
		} catch (SQLException e) {
		System.out.println("getBid");
		e.printStackTrace();
		}
		closeConnection();
		return bid;
	}

	@Override
	public ArrayList<Bid> getBidsFromAuction(int auctionId) {
		connect();
		ArrayList<Bid> bids = new ArrayList<Bid>();
		try {
			getBidsFromAuction = connection.prepareStatement("SELECT * FROM Bid WHERE auctionId=?");
			getBidsFromAuction.setInt(1,auctionId);
			ResultSet rs = getBidsFromAuction.executeQuery();
			while (rs.next()) {
				bids.add(new Bid(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getDate(5)));
			}
		} catch (SQLException e) {
			System.out.println("getBidsFromAuction");
			e.printStackTrace();
		}
		closeConnection();
		return bids;
	}

	@Override
	public ArrayList<Bid> getBidsFromUser(int userId) {
		connect();
		ArrayList<Bid> bids = new ArrayList<Bid>();
		try {
			getBidsFromUser = connection.prepareStatement("SELECT * FROM Bid WHERE userId=?");
			getBidsFromUser.setInt(1,userId);
			ResultSet rs = getBidsFromUser.executeQuery();
			while (rs.next()) {
				bids.add(new Bid(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getDate(5)));
			}
		} catch (SQLException e) {
			System.out.println("getBidsFromUser");
			e.printStackTrace();
		}
		closeConnection();
		return bids;
	}

	@Override
	public Category getCategory(int categoryId) {
		connect();
		Category category = null;
		try {
			getCategory = connection.prepareStatement("SELECT * FROM Category WHERE id=?");
			getCategory.setInt(1,categoryId);
			ResultSet rs = getCategory.executeQuery();
			if (rs.next()) {
				category = new Category(rs.getInt(1),rs.getString(2));
			}
		} catch (SQLException e) {
			System.out.println("getCategory");
			e.printStackTrace();
		}
		closeConnection();
		return category;
	}

	@Override
	public int getCreditsFromUser(int userId) {
		connect();
		int credits = -1;
		try {
			getCreditsFromUser = connection.prepareStatement("SELECT credits FROM Users WHERE userid=?");
			getCreditsFromUser.setInt(1, userId);
			ResultSet rs = getCreditsFromUser.executeQuery();
			rs.next();
			credits = rs.getInt(1);
		} catch (SQLException e) {
			System.out.println("getCreditsFromUser");
			e.printStackTrace();
		}
		closeConnection();
		return credits;
	}

	@Override
	public User getUser(int userId) {
		connect();
		User user = null;
		try {
			getUser = connection.prepareStatement("SELECT * FROM Users WHERE userid=?");
			getUser.setInt(1,userId);
			ResultSet rs = getUser.executeQuery();
			rs.next();
			user = new User(userId,rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11));
		} catch (SQLException e) {
			System.out.println("getUser int");
			e.printStackTrace();
		}
		closeConnection();
		return user;
	}
	
	@Override
	public User getUser(String email) {
		connect();
		User user = null;
		try {
			getUser = connection.prepareStatement("SELECT * FROM Users WHERE email=?");
			getUser.setString(1,email);
			ResultSet rs = getUser.executeQuery();
			rs.next();
			user = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11));
		} catch (SQLException e) {
			System.out.println("getUser String");
			e.printStackTrace();
		}
		closeConnection();
		return user;
	}

	@Override
	public boolean insertAuction(Auction auction) {
		connect();
		int rs = 0;
		try {
			insertAuction = connection.prepareStatement("INSERT INTO Auction (id,authorId,categoryId,title,description,startprice,endDate,visible) VALUES (null,?,?,?,?,?,?,?)");
			insertAuction.setInt(1,auction.getAuthor_id());
			insertAuction.setInt(2,auction.getCategory_id());
			insertAuction.setString(3,auction.getTitle());
			insertAuction.setString(4,auction.getDescription());
			insertAuction.setInt(5,auction.getStartprice());
			insertAuction.setDate(6,auction.getEndDate());
			insertAuction.setInt(7,auction.getVisible());
			rs = insertAuction.executeUpdate();
		} catch (SQLException e) {
			System.out.println("insertAuction");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean insertBid(Bid bid) {
		connect();
		int rs = 0;
		try {
			insertBid = connection.prepareStatement("INSERT INTO Bid (id,userId,auctionId,credits,bidDate) VALUES (null,?,?,?,?)");
			insertBid.setInt(1,bid.getUser_id());
			insertBid.setInt(2,bid.getAuction_id());
			insertBid.setInt(3,bid.getCredits());
			insertBid.setDate(4,bid.getDate());
			rs = insertBid.executeUpdate();
		} catch(SQLException e) {
			System.out.println("insertBid");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean insertCategory(Category category) {
		connect();
		int rs = 0;
		try {
			insertCategory = connection.prepareStatement("INSERT INTO Category (id,title) VALUES (null,?)");
			insertCategory.setString(1,category.getTitle());
			System.out.println(insertCategory.toString());
			rs = insertCategory.executeUpdate();
		} catch (SQLException e) {
			System.out.println("insertCategory");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	public boolean insertUser(User user) {
		connect();
		int rs = 0;
		try {
			insertUser = connection.prepareStatement("INSERT INTO Users (userid,name,address,residence,birthdate,maybid,mayoffer,isadmin,credits,password,email) VALUES (null,?,?,?,?,?,?,?,?,?,?)");
			insertUser.setString(1,user.getName());
			insertUser.setString(2,user.getAddress());
			insertUser.setString(3,user.getResidence());
			insertUser.setDate(4,user.getBirthDate());
			insertUser.setInt(5,user.getMaybid());
			insertUser.setInt(6,user.getMayoffer());
			insertUser.setInt(7,user.getIsadmin());
			insertUser.setInt(8,user.getCredits());
			insertUser.setString(9,user.getPassword());
			insertUser.setString(10,user.getEmail());
			rs = insertUser.executeUpdate();
		} catch (SQLException e) {
			System.out.println("insertUser");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean saveAuction(Auction auction) {
		connect();
		int rs = 0;
		try {
			saveAuction = connection.prepareStatement("UPDATE Auction SET authorID=?,categoryId=?,title=?,description=?,startprice=?,endDate=?,visible=? WHERE id=?");
			saveAuction.setInt(1,auction.getAuthor_id());
			saveAuction.setInt(2,auction.getCategory_id());
			saveAuction.setString(3,auction.getTitle());
			saveAuction.setString(4,auction.getDescription());
			saveAuction.setInt(5,auction.getStartprice());
			saveAuction.setDate(6,auction.getEndDate());
			saveAuction.setInt(7,auction.getVisible());
			saveAuction.setInt(8,auction.getId());
			rs = saveAuction.executeUpdate();
		} catch (SQLException e) {
			System.out.println("saveAuction");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean saveBid(Bid bid) {
		connect();
		int rs = 0;
		try {
			saveBid = connection.prepareStatement("UPDATE Bid SET userId=?,auctionId=?,credits=?,bidDate=? WHERE id=?");
			saveBid.setInt(1,bid.getUser_id());
			saveBid.setInt(2,bid.getAuction_id());
			saveBid.setInt(3,bid.getCredits());
			saveBid.setDate(4,bid.getDate());
			saveBid.setInt(5,bid.getId());
			rs = saveBid.executeUpdate();
		} catch (SQLException e) {
			System.out.println("saveBid");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean saveCategory(Category category) {
		connect();
		int rs = 0;
		try {
			saveCategory = connection.prepareStatement("UPDATE Category SET title=? WHERE id=?");
			saveCategory.setString(1,category.getTitle());
			saveCategory.setInt(2,category.getId());
			rs = saveCategory.executeUpdate();
		} catch (SQLException e) {
			System.out.println("saveCategory");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}

	@Override
	public boolean saveUser(User user) {
		connect();
		int rs = 0;
		try {
			if (user.checkPassword("-1")) {
				saveUser = connection.prepareStatement("UPDATE Users SET name=?,address=?,residence=?,birthdate=?,maybid=?,mayoffer=?,isadmin=?,credits=?,email=? WHERE userid=?");
				saveUser.setString(1,user.getName());
				saveUser.setString(2,user.getAddress());
				saveUser.setString(3,user.getResidence());
				saveUser.setDate(4,user.getBirthDate());
				saveUser.setInt(5,user.getMaybid());
				saveUser.setInt(6,user.getMayoffer());
				saveUser.setInt(7,user.getIsadmin());
				saveUser.setInt(8,user.getCredits());
				saveUser.setString(9,user.getEmail());
				saveUser.setInt(10,user.getId());
			} else {
				saveUser = connection.prepareStatement("UPDATE Users SET name=?,address=?,residence=?,birthdate=?,maybid=?,mayoffer=?,isadmin=?,credits=?,password=?,email=? WHERE userid=?");
				saveUser.setString(1,user.getName());
				saveUser.setString(2,user.getAddress());
				saveUser.setString(3,user.getResidence());
				saveUser.setDate(4,user.getBirthDate());
				saveUser.setInt(5,user.getMaybid());
				saveUser.setInt(6,user.getMayoffer());
				saveUser.setInt(7,user.getIsadmin());
				saveUser.setInt(8,user.getCredits());
				saveUser.setString(9,user.getPassword());
				saveUser.setString(10,user.getEmail());
				saveUser.setInt(11,user.getId());
			}
			rs = saveUser.executeUpdate();
		} catch (SQLException e) {
			System.out.println("saveUSer");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}
	
	@Override
	public User getUserFromBid(int bidId) {
		connect();
		User user = null;
		try {
			getUserFromBid = connection.prepareStatement("SELECT * FROM Users,Bid WHERE userid=userId AND id=?");
			getUserFromBid.setInt(1,bidId);
			ResultSet rs = getUserFromBid.executeQuery();
			rs.next();
			user = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11));
		} catch (SQLException e) {
			System.out.println("getUserFromBid");
			e.printStackTrace();
		}
		closeConnection();
		return user;
	}

	@Override
	public ArrayList<Auction> getAuctions() {
		connect();
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			getAuctions = connection.prepareStatement("SELECT id,authorId,categoryId,title,description,startprice,endDate,visible FROM Auction WHERE visible = 1 ORDER BY enddate ASC");
			ResultSet rs = getAuctions.executeQuery();
			while (rs.next()) {
				auctions.add(new Auction(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getDate(7),rs.getInt(8)));
			}
		} catch (SQLException e) {
			System.out.println("getAuctions");
			e.printStackTrace();
		}
		closeConnection();
		return auctions;
	}
	
	public ArrayList<Auction> getAuctionsAdmin() {
		connect();
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			getAuctionsadmin = connection.prepareStatement("SELECT id,authorId,categoryId,title,description,startprice,endDate,visible FROM Auction ORDER BY enddate ASC");
			ResultSet rs = getAuctionsadmin.executeQuery();
			while (rs.next()) {
				auctions.add(new Auction(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getDate(7),rs.getInt(8)));
			}
		} catch (SQLException e) {
			System.out.println("getAuctionsadmin");
			e.printStackTrace();
		}
		closeConnection();
		return auctions;
	}

	@Override
	public ArrayList<Category> getCategorys() {
		connect();
		ArrayList<Category> categories = new ArrayList<Category>();
		try {
			getCategorys = connection.prepareStatement("SELECT * FROM Category");
			ResultSet rs = getCategorys.executeQuery();
			//System.out.println(rs.toString());
			while (rs.next()) {
				categories.add(new Category(rs.getInt(1),rs.getString(2)));
			}
		} catch (SQLException e) {
			System.out.println("getCategorys");
			e.printStackTrace();
		}
		closeConnection();
		return categories;
	}

	@Override
	public ArrayList<User> getUsers() {
		connect();
		ArrayList<User> users = new ArrayList<User>();
		try {
			getUsers = connection.prepareStatement("SELECT * FROM Users");
			ResultSet rs = getUsers.executeQuery();
			while (rs.next()) {
				users.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11)));
			}
		} catch (SQLException e) {
			System.out.println("getUsers");
			e.printStackTrace();
		}
		closeConnection();
		return users;
	}

	@Override
	public boolean deleteCategory(int id) {
		connect();
		int rs = 0;
		try {
			deleteCategory = connection.prepareStatement("DELETE FROM category WHERE id=?");
			deleteCategory.setInt(1,id);
			rs = deleteCategory.executeUpdate();
		} catch (SQLException e) {
			System.out.println("deleteCategory");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		} else {
			closeConnection();
			return false;
		}
	}
	
	public int getAantal(String code){
		connect();
		int i = 0;
		try {
			getAantalCredits = connection.prepareStatement("SELECT aantalcredits FROM coupon WHERE code=?");
			getAantalCredits.setString(1, code);
			ResultSet rs = getAantalCredits.executeQuery();
			if(rs.next()) {
				i = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeConnection();
			return 0;
		}
		closeConnection();
		return i;
	}
	
	public boolean verwijderCoupon(String code){
		connect();
		try {
			deleteCoupon = connection.prepareStatement("DELETE FROM coupon WHERE code=?");
			deleteCoupon.setString(1, code);
			deleteCoupon.executeUpdate();
			closeConnection();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			closeConnection();
			return false;
		}
	}
	
	public void deleteAuction(int id){
		if (deleteAllBidsFromAuction(id)) {
			connect();
			try {
				deleteAuction = connection.prepareStatement("DELETE FROM auction WHERE id=?");
				deleteAuction.setInt(1,id);
				deleteAuction.executeUpdate();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public boolean deleteAllBidsFromAuction(int id){
		connect();
		try {
			deleteAllBidsFromAuction = connection.prepareStatement("DELETE FROM bid WHERE auctionid=?");
			deleteAllBidsFromAuction.setInt(1,id);
			deleteAllBidsFromAuction.executeUpdate();
			closeConnection();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Bid getHighestBid(String string,int year,int month,int week,int day) {
		connect();
		ResultSet rs = null;
		Bid bid = null;
		try {
			if (string.equals("year")) {
				getHighestBid = connection.prepareStatement("SELECT * FROM (SELECT id,userid,auctionid,credits,biddate,RANK() OVER (ORDER BY credits DESC,ROWNUM ASC) RN FROM bid WHERE bidDate>=to_date('01-01-"+year+"','dd-mm-yyyy') AND bidDate<=to_date('31-12-"+year+"','dd-mm-yyyy')) WHERE RN <= 1");
			} else if (string.equals("month")) {
				Calendar calendar = Calendar.getInstance();
				calendar.clear();
				calendar.set(Calendar.MONTH, (month-1));
				calendar.set(Calendar.YEAR, year);
				int endOfMonth = calendar.getActualMaximum(calendar.MONTH);
				getHighestBid = connection.prepareStatement("SELECT * FROM (SELECT id,userid,auctionid,credits,biddate,RANK() OVER (ORDER BY credits DESC,ROWNUM ASC) RN FROM bid WHERE bidDate>=to_date('01-"+month+"-"+year+"','dd-mm-yyyy') AND bidDate<=to_date('"+endOfMonth+"-"+month+"-"+year+"','dd-mm-yyyy')) WHERE RN <= 1");
			} else if (string.equals("week")) {
				Calendar calendar = Calendar.getInstance();
				calendar.clear();
				calendar.set(Calendar.WEEK_OF_YEAR, week);
				calendar.set(Calendar.YEAR, year);
				int startDay = calendar.DAY_OF_MONTH;
				int endMonth = calendar.MONTH;
				int endDay = startDay+6;
				if (calendar.getActualMaximum(calendar.DAY_OF_MONTH) < endDay) {
					endDay -= calendar.getActualMaximum(calendar.DAY_OF_MONTH);
					endMonth++;
				}
				getHighestBid = connection.prepareStatement("SELECT * FROM (SELECT id,userid,auctionid,credits,biddate,RANK() OVER (ORDER BY credits DESC,ROWNUM ASC) RN FROM bid WHERE bidDate>=to_date('"+startDay+"-"+calendar.MONTH+"-"+year+"','dd-mm-yyyy') AND bidDate<=to_date('"+endDay+"-"+endMonth+"-"+year+"','dd-mm-yyyy')) WHERE RN <= 1");
			} else {
				getHighestBid = connection.prepareStatement("SELECT * FROM (SELECT id,userid,auctionid,credits,biddate,RANK() OVER (ORDER BY credits DESC,ROWNUM ASC) RN FROM bid WHERE bidDate=to_date('"+day+"-"+month+"-"+year+"','dd-mm-yyyy')) WHERE RN <= 1");
			}
			
			rs = getHighestBid.executeQuery();
			if (rs.next()) {
				bid = new Bid(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getDate(5));
			}
		} catch(SQLException e) {
			System.out.println("getHighestBid type: "+string);
			System.out.println("year "+year+" month "+month+" week "+week+" day "+day);
			e.printStackTrace();
		}
		closeConnection();
		return bid;
	}
	
	public ArrayList<Integer> getYears() {
		connect();
		ArrayList<Integer> years = new ArrayList<Integer>();
		try {
			getYearsStart = connection.prepareStatement("SELECT * FROM (SELECT endDate,RANK() OVER (ORDER BY endDate ASC,ROWNUM ASC ) RN FROM auction ) WHERE RN <=1");
			getYearsEnd = connection.prepareStatement("SELECT * FROM (SELECT endDate,RANK() OVER (ORDER BY endDate DESC,ROWNUM ASC ) RN FROM auction ) WHERE RN <=1");
			ResultSet rsStart = getYearsStart.executeQuery();
			ResultSet rsEnd = getYearsEnd.executeQuery();
			rsStart.next();
			rsEnd.next();
			int start = rsStart.getDate(1).getYear()+1900;
			int end = rsEnd.getDate(1).getYear()+1900;
			for(int i=start;i<=end;i++) {
				years.add(i);
			}
		} catch(SQLException e) {
			System.out.println("getYears");
			e.printStackTrace();
		}
		closeConnection();
		return years;
	}
	
	public String getNameOfUser(int userId) {
		connect();
		User user = null;
		try {
			getNameOfUser = connection.prepareStatement("SELECT * FROM Users WHERE userid=?");
			getNameOfUser.setInt(1,userId);
			ResultSet rs = getNameOfUser.executeQuery();
			rs.next();
			user = new User(userId,rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11));
		} catch (SQLException e) {
			System.out.println("getNameOfUser int");
			e.printStackTrace();
		}
		closeConnection();
		return user.getName();
	}
	
	public boolean deleteBid(int bidId) {
		connect();
		int rs = 0;
		try {
			deleteBid = connection.prepareStatement("DELETE FROM bid WHERE id=?");
			deleteBid.setInt(1,bidId);
			rs = deleteBid.executeUpdate();
		} catch (SQLException e) {
			System.out.println("deleteBid");
			e.printStackTrace();
		}
		if (rs == 1) {
			closeConnection();
			return true;
		}
		else{
			closeConnection();
			return false;
		}
	}

	public ArrayList<Auction> getBestBidAuctions() {
		connect();
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			getAuctions = connection.prepareStatement("SELECT auctionid,COUNT(credits) as nr FROM Bid GROUP BY auctionid ORDER BY nr DESC");
			ResultSet rs = getAuctions.executeQuery();
			int i = 0;
			while (rs.next() && i <= 10) {
				Auction auction = getAuction(rs.getInt(1));
				auction.setNumberOfBids(rs.getInt(2));
				auctions.add(auction);
				i++;
			}
		} catch (SQLException e) {
			System.out.println("getBestBidAuctions");
			e.printStackTrace();
		}
		closeConnection();
		return auctions;
	}
}